﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Configuration;
using System.IO;
using System.Data;
using System.Text;
using Com.FoxGlobal.DanielChan.Hyperion.HFMProxy;

namespace Com.FoxGlobal.Daniel.Midea.TemplateService
{
    /// <summary>
    /// Summary description for ODSService
    /// </summary>
    [WebService(Namespace = "http://tempuri.org/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [System.ComponentModel.ToolboxItem(false)]
    // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
    // [System.Web.Script.Services.ScriptService]
    public class ODSService : WebServiceBase
    {

        /// <summary>
        /// Description:  初始化HFMProxy
        /// Calls:            
        /// Called By:     
        /// Parameter:   
        /// Others:         
        /// </summary>
        /// <returns></returns>
        private HFMProxy InitAgent()
        {
            HFMProxy agent = new HFMProxy();
            return agent;
        }

        /// <summary>
        /// Description:  获取服务器端的版本号
        /// Calls:            
        /// Called By:     Excel VBA
        /// Parameter:   
        /// Others:         版本号使用  YYYYMMDD 的方式来标识，如 20100707, 20100729等
        /// </summary>
        /// <returns>服务器端版本信息</returns>
        [WebMethod(EnableSession = true)]
        public WebServiceResult GetServerVersion()
        {
            Logging.WriteLog(LoggingLevel.DEBUG, "GetServerVersion()");

            return new WebServiceResult(WebServiceModuleCode.ODS, WebServiceErrorCode.Success, ConfigurationManager.AppSettings["Server:ODS:Version"]);
        }

        /// <summary>
        /// Description:  比较服务器与客户端之间的版本是否匹配
        /// Calls:            
        /// Called By:     Excel VBA
        /// Parameter:   strClientVersion         客户端版本
        /// Others:         1、版本号使用  YYYYMMDD 的方式来标识，如 20100707, 20100729等
        ///                     2、服务器版本  > 客户端版本              False
        ///                     3、服务器版本 <= 客户端版本             True
        /// </summary>
        /// <returns>True/False ==>  匹配/不匹配</returns>
        [WebMethod(EnableSession = true)]
        public WebServiceResult CheckVersion(string strCategory, Int64 strClientVersion)
        {
            Logging.WriteLog(LoggingLevel.DEBUG, "CheckVersion(Category={0}, Version={1})", strCategory, strClientVersion);

            if (Application["ExcelClient"] == null)
            {
                Application["ExcelClient"] = new Dictionary<String, String>();
            }

            ((Dictionary<String, String>)Application["ExcelClient"])[HttpContext.Current.Request.UserHostName] = strCategory;

            return new WebServiceResult(WebServiceModuleCode.ODS, WebServiceErrorCode.Success, (Int64.Parse(ConfigurationManager.AppSettings[String.Format ("Server:{0}:Version", strCategory)]) <= strClientVersion).ToString());

            
        }

        /// <summary>
        /// Description:  获取最新的模板地址
        /// Calls:            
        /// Called By:     Excel VBA
        /// Parameter:   
        /// Others:         1、版本号使用  YYYYMMDD 的方式来标识，如 20100707, 20100729等
        ///                     2、服务器版本  > 客户端版本              False
        ///                     3、服务器版本 <= 客户端版本             True
        /// </summary>
        /// <returns>True/False ==>  匹配/不匹配</returns>
        [WebMethod(EnableSession = true)]
        public WebServiceResult GetLastestTemplateURL()
        {
            Logging.WriteLog(LoggingLevel.DEBUG, "GetLastestTemplateURL()");

            return new WebServiceResult(WebServiceModuleCode.ODS, WebServiceErrorCode.Success, ConfigurationManager.AppSettings["Server:ODS:LastestTemplateURL"]);
        }


        /// <summary>
        /// Description:  
        /// Calls:            
        /// Called By:     
        /// Parameter:   
        /// Others:         
        /// </summary>
        /// <returns></returns>
        [WebMethod(EnableSession = true)]
        public WebServiceResult CheckDimensions(string tablename, string metafiled, string datafiled)
        {
            Logging.WriteLog(LoggingLevel.DEBUG, "CheckDimensions (Table={0}, Meta={1}, Data={2})", tablename, metafiled, datafiled);

            return new WebServiceResult(WebServiceModuleCode.ODS, WebServiceErrorCode.Success, "");
        }

        /// <summary>
        /// Description:  登录HFM
        /// Calls:            
        /// Called By:     Excel VBA
        /// Parameter:   Session
        /// Others:         
        /// </summary>
        /// <returns>True/False ==>  匹配/不匹配</returns>
        [WebMethod(EnableSession = true)]
        public WebServiceResult Login(string username, string password)
        {
            Logging.WriteLog(LoggingLevel.DEBUG, "Login (Username={0}, Password=Hash({1}))", username, password.GetHashCode());

            HFMProxy agent = InitAgent();

            try
            {
                this.Session["Cluster"] = ConfigurationManager.AppSettings["ODS:HFM:Cluster"].ToString();
                this.Session["Appname"] = ConfigurationManager.AppSettings["ODS:HFM:Applicaton"].ToString();
                this.Session["Domain"] = ConfigurationManager.AppSettings["ODS:HFM:Domain"].ToString();
                this.Session["Username"] = username;
                this.Session["Token"] = ConfigurationManager.AppSettings["ODS:HFM:Token"].ToString();
                this.Session["Password"] = password;

                HFMLogin(agent);
                return new WebServiceResult(WebServiceModuleCode.ODS, WebServiceErrorCode.Success, "");
            }
            catch (WebServiceException ex)
            {
                return ex.Result;
            }
            catch (Exception ex)
            {
                return new WebServiceResult(WebServiceModuleCode.ODS, WebServiceErrorCode.Error_Login_Failture, ex.ToString());
            }
        }


        /// <summary>
        /// Description:  登录HFM
        /// Calls:            
        /// Called By:     Excel VBA
        /// Parameter:   Session
        /// Others:         
        /// </summary>
        /// <returns>True/False ==>  匹配/不匹配</returns>
        [WebMethod(EnableSession = true)]
        public WebServiceResult CheckSecurity(string username, string system, string entity, string scenario, string year, string period, int priority)
        {
            //用户访问CheckSecurity接口，根据 POV (Scenario, Year, Perio, Entity) 做权限认证
            //对于数据读操作，权限要求是 HFM_ACCESS_RIGHTS_READONLY (2)
            //对于数据写操作或清除操作，权限要求是 HFM_ACCESS_RIGHTS_ALL (4)
            Logging.WriteLog(LoggingLevel.DEBUG, "Username={0}, System={1}, Entity={2}, Scenario={3}, Year={4}, Period={5}, Priority={6}",
                                        username, system, entity, scenario, year, period, priority);

            //HFMProxy在这里的作用，是为了检查用户是否有指定的POV的数据访问权限（读/写），以及检查数据是否已经提升到特定的Level以上
            //其中ReviewLevel的权限要求，是根据美的企业合并流程决定，Review Level 3及以上的数据不允许通过模板修改 (20110316)
            HFMProxy agent = InitAgent();

            //DBAgent在这里的作用，是为了判断用户是否使用了正确的模板
            DBAgent dbAgent = new DBAgent();

            try
            {
                //通过将 (HostAddress : ExcelCategory) 存储在Appliation变量中，这里获取到具体的模板类型
                //根据模板类型，以及Entity是否在Mapping表中出现（是否ERP实体），来判断模板使用是否正确
                String Category = ((Dictionary<String, String>)Application["ExcelClient"])[HttpContext.Current.Request.UserHostAddress].ToString();

                //Step1： 检查用户是否使用了正确的Excel输入模板
                dbAgent.Connect();
                if (dbAgent.CheckEntity(Category, entity))
                {

                    //Step2：检查用户是否具备指定POV的访问权限（读或写）
                    HFMLogin(agent);
                    Boolean bCellSecurity = Boolean.Parse(agent.CheckSecurity(scenario, year, period, entity,
                                                    ConfigurationManager.AppSettings["HFM:Security:Parent"],
                                                    ConfigurationManager.AppSettings["HFM:Security:Value"],
                                                    ConfigurationManager.AppSettings["HFM:Security:Account"],
                                                    ConfigurationManager.AppSettings["HFM:Security:ICP"],
                                                    ConfigurationManager.AppSettings["HFM:Security:Custom1"],
                                                    ConfigurationManager.AppSettings["HFM:Security:Custom2"],
                                                    ConfigurationManager.AppSettings["HFM:Security:Custom3"],
                                                    ConfigurationManager.AppSettings["HFM:Security:Custom4"],
                                                    priority).ToString());

                    //Step3: 检查数据是否已经提升到指定的Review Level以上
                    Boolean bReviewLevelSecurity = true;
                    Int32 iMinReivewLevel = Int32.Parse(ConfigurationManager.AppSettings["ODS:DATALOCK:MINREVIEWLEVEL"]);
                    Int32 iMaxReivewLevel = Int32.Parse(ConfigurationManager.AppSettings["ODS:DATALOCK:MAXREVIEWLEVEL"]);
                    Int32 iBaseRequest = Int32.Parse(ConfigurationManager.AppSettings["ODS:DATALOCK:REQUEST"]);
                    Int32 iPhase = Int32.Parse(ConfigurationManager.AppSettings["ODS:DATALOCK:PHASE"]);
                    Int32 iCurrReviewLevel = -1;

                    //美的场景：如果数据已经提升到Review Level 3（及以上），则不允许用户修改或清除数据
                    //这里做了两个判断：1）用户请求的操作是否比配置文件中的操作级别高（配置文件中默认配置是4），这是控制读/写功能是否受影响的环节
                    //                            2）判断配置文件中的ReviewLeve是否已经配置正确，在范围 [0, 10]之间，其它的配置值将视同为不启用ReviewLevel的判断
                    if (iMinReivewLevel >= 0 && iMinReivewLevel <= 15 && iMaxReivewLevel >= 0 && iMaxReivewLevel <= 15 && iMinReivewLevel <= iMaxReivewLevel && priority >= iBaseRequest)
                    {
                        if (iPhase > 0)
                        {
                            //如果配置值中的ReviewLevel（例如3），介于MinReviewLevel和MaxReviewLevel之间，则表示用户可以继续操作数据
                            iCurrReviewLevel = agent.GetProcessLevelEx(scenario, year, period, ConfigurationManager.AppSettings["HFM:Security:Value"], entity, iPhase);
                            bReviewLevelSecurity = (iCurrReviewLevel >= iMinReivewLevel) && (iCurrReviewLevel <= iMaxReivewLevel);
                        }
                        else
                        {
                            //如果配置值中的ReviewLevel（例如3），介于MinReviewLevel和MaxReviewLevel之间，则表示用户可以继续操作数据
                            iCurrReviewLevel = agent.GetProcessLevel(scenario, year, period, ConfigurationManager.AppSettings["HFM:Security:Value"], entity);
                            bReviewLevelSecurity = (iCurrReviewLevel >= iMinReivewLevel) && (iCurrReviewLevel <= iMaxReivewLevel);
                        }
                        
                    }
                    else
                    {
                        bReviewLevelSecurity = true;
                    }

                    //记录权限检查的中间变量
                    Logging.WriteLog(LoggingLevel.DEBUG, "bCell: {0}, bReviewLevel: {1}, iCurrReivewLevel: {2}, iBaseRequest: {3}, iMinReviewLevel: {4}, iMaxReviewLevel: {5}", bCellSecurity, bReviewLevelSecurity, iCurrReviewLevel, iBaseRequest, iMinReivewLevel, iMaxReivewLevel);

                    //如果用户权限同时满足Review Level和POV Security的情况下，允许用户操作数据，否则提示用户权限不足
                    if (bCellSecurity && bReviewLevelSecurity)
                    {
                        return new WebServiceResult(WebServiceModuleCode.ODS, WebServiceErrorCode.Success, "True");
                    }
                    else
                    {
                        return new WebServiceResult(WebServiceModuleCode.ODS, WebServiceErrorCode.Error_Security_Not_Enough, Convert.ToString((int)WebServiceErrorCode.Error_Security_Not_Enough));
                    }
                }
                else
                {
                    return new WebServiceResult(WebServiceModuleCode.ODS, WebServiceErrorCode.Error_Template_Error, Convert.ToString((int)WebServiceErrorCode.Error_Template_Error));
                }
            }
            catch (WebServiceException ex)
            {
                Logging.WriteLog(LoggingLevel.ERROR, "Security Check Error: {0}", ex.ToString());
                return new WebServiceResult(WebServiceModuleCode.ODS, WebServiceErrorCode.Error_Security_Not_Enough, Convert.ToString((int)WebServiceErrorCode.Error_Security_Not_Enough));
            }
            catch (Exception ex)
            {
                Logging.WriteLog(LoggingLevel.ERROR, "Security Check Error: {0}", ex.ToString());
                return new WebServiceResult(WebServiceModuleCode.ODS, WebServiceErrorCode.Error_Security_Not_Enough, Convert.ToString((int)WebServiceErrorCode.Error_Security_Not_Enough));
            }
            finally
            {
                dbAgent.Disconnct();
            }
        }


        /// <summary>
        /// Description:  登录 
        /// Calls:            
        /// Called By:     
        /// Parameter:   
        /// Others:         
        /// </summary>
        /// <returns></returns>
        private void HFMLogin(HFMProxy agent)
        {
            //Logging.WriteLog(LoggingLevel.DEBUG, "HFM Login (Cluster={0}, Application={1}, Domain={2}, Username={3}, Token={4}, Password={5})",
            //                            this.Session["Cluster"], this.Session["Appname"], this.Session["Domain"],
            //                            this.Session["Username"], this.Session["Token"], this.Session["Password"]);
            agent.Login(this.Session["Cluster"].ToString(), this.Session["Appname"].ToString(), this.Session["Domain"].ToString(),
                this.Session["Username"].ToString(), this.Session["Token"].ToString(), this.Session["Password"].ToString());
        }


       
        /// <summary>
        /// Description:  
        /// Calls:            
        /// Called By:     
        /// Parameter:   
        /// Others:         
        /// </summary>
        /// <returns></returns>
        [WebMethod(EnableSession = true)]
        public WebServiceResult Submit(string sourcetable, string targetable, string pov, string column, string row, string datafiled, string datavalues)
        {

            Logging.WriteLog(LoggingLevel.DEBUG, "Submit (sourcetable='{0}', targettable='{1}', pov='{2}', column='{3}', row='{4}', datafield='{5}', datavalue='{6}'",
                                                                            sourcetable, targetable, pov, column, row, datafiled, datavalues);

            DBAgent agent = new DBAgent();
            POVManager povManager = null;

            try
            {
                povManager = new POVManager(sourcetable, targetable, pov, column, row, "", datafiled, datavalues);

                agent.CheckDimension(Server.MapPath(ConfigurationManager.AppSettings["ODS:MetaTableFile"]), povManager.SourceTable.First().Value.Name, povManager.GetKeyColumns(), povManager.GetDataColumns(), "SET");

                DataTable datatable = GetSubmitDataTable(povManager);

                if (datatable.Rows.Count > 0)
                {
                    agent.Connect();

                    agent.BeforeUpdate(povManager.TargetTable.First().Value.Name);

                    agent.BatchUpdate(povManager.TargetTable.First().Value.Name, povManager.GetKeyColumns(), povManager.GetDataColumns(), povManager.GetExtraColumns().ToArray(), datatable, 200);

                    agent.CheckMetaData(Server.MapPath(ConfigurationManager.AppSettings["ODS:MetaTableFile"]), povManager.TargetTable.First().Value.Name, povManager.TargetTable.First().Value.Name, agent.TablePrefix, "SET");

                    agent.ProcessData(povManager.TargetTable.First().Value.Name, povManager.SourceTable.First().Value.Name, String.Join("and", povManager.GetComparsion()), String.Join(",", povManager.GetColumns()), string.Join(",", povManager.GetKeySet()), string.Join("", povManager.GetPOVDefine()));
                }
                    
                return new WebServiceResult(WebServiceModuleCode.ODS, WebServiceErrorCode.Success, "");

            }
            catch (WebServiceException ex)
            {
                return ex.Result;
            }
            catch (Exception ex)
            {
                Logging.WriteLog(LoggingLevel.ERROR, "{0}", ex.ToString());
                return new WebServiceResult(WebServiceModuleCode.ODS, WebServiceErrorCode.Error_Retrieve_Failture, ex.ToString());
            }
            finally
            {
                try
                {
                    agent.AfterUpdate(povManager.TargetTable.First().Value.Name);
                }
                catch (Exception ex)
                {
                    Logging.WriteLog(LoggingLevel.WARNING, "{0}", ex.ToString());
                }

                agent.Disconnct();
            }
        }

        public DataTable GetSubmitDataTable(POVManager povManager)
        {
            DataTable datatable = new DataTable();

            foreach (string columnname in povManager.GetKeyColumns())
            {
                datatable.Columns.Add(columnname);
            }
            foreach (string columnname in povManager.GetExtraColumns())
            {
                datatable.Columns.Add(columnname);
            }
            foreach (string columnname in povManager.GetDataColumns())
            {
                datatable.Columns.Add(columnname);
            }

            datatable.TableName = povManager.SourceTable.First().Value.Name;

            RangeDefine each_col, each_row;

            if (povManager.Columns.Count > 0)
            {
                Logging.WriteLog(LoggingLevel.DEBUG, "Single Column Mode");
                foreach (RangeDefine each_data in povManager.DataValues.Values)
                {
                    try
                    {
                        Double.Parse(each_data.Value);
                    }
                    catch
                    {
                        continue;
                    }

                    each_col = povManager.Columns[each_data.Column];
                    each_row = povManager.Rows[each_data.Row];

                    DataRow datarow = datatable.NewRow();
                    foreach (RangeDefine each_pov in povManager.POVs.Values)
                    {
                        datarow[each_pov.Name] = each_pov.Value;
                    }
                    datarow["ColumnIDX"] = each_col.Column;
                    datarow["RowIDX"] = each_row.Row;
                    datarow["Username"] = Session["Username"];

                    string[] names = (each_col.Name + "$" + each_row.Name).Trim('$').Split('$');
                    string[] values = (each_col.Value + "$" + each_row.Value).Trim('$').Split('$');

                    if (names.Length != values.Length)
                    {
                        throw new WebServiceException(WebServiceErrorCode.Error_Retrieve_Failture, String.Format("({0}, {1}) col.name={2}, col.value={3}, row.name={4}, row.value={5}", each_col.Column, each_col.Row, each_col.Name, each_col.Value, each_row.Name, each_row.Value));
                    }

                    for (int index = 0; index < names.Length; ++index)
                    {
                        datarow[names[index]] = values[index];
                    }

                    datarow[povManager.GetDataColumns()[0]] = each_data.Value;

                    datatable.Rows.Add(datarow);
                }
            }
            else
            {
                Logging.WriteLog(LoggingLevel.DEBUG, "Multiple Column Mode");
                foreach(RangeDefine rowitem in povManager.Rows.Values)
                {
                    DataRow datarow = datatable.NewRow();
                    foreach (RangeDefine each_pov in povManager.POVs.Values)
                    {
                        datarow[each_pov.Name] = each_pov.Value;
                    }
                    datarow["ColumnIDX"] = "-1";
                    datarow["RowIDX"] = rowitem.Row;
                    datarow["Username"] = Session["Username"];

                    string[] names = rowitem.Name.Trim('$').Split('$');
                    string[] values = rowitem.Value.Trim('$').Split('$');

                    if (names.Length != values.Length)
                    {
                        throw new WebServiceException(WebServiceErrorCode.Error_Retrieve_Failture, String.Format("({0}, {1}) row.name={2}, row.value={3}", -1, rowitem.Row, rowitem.Name, rowitem.Value));
                    }

                    for (int index = 0; index < names.Length; ++index)
                    {
                        datarow[names[index]] = values[index];
                    }

                    foreach (RangeDefine fielditem in povManager.DataFields.Values)
                    {
                        string itemkey = String.Format("{0}%{1}", fielditem.Column, rowitem.Row);
                        if (povManager.DataValues.Keys.Contains(itemkey))
                        {
                            RangeDefine each_data = povManager.DataValues[itemkey];

                            try
                            {
                                //Double.Parse(each_data.Value);
                            }
                            catch
                            {
                                continue;
                            }

                            datarow[fielditem.Name] = each_data.Value;
                        }
                        else
                        {
                            Logging.WriteLog(LoggingLevel.ERROR, "Item key not found in data: '{0}'. '{1}'", itemkey, string.Join(",", povManager.DataValues.Keys));
                        }
                    }

                    datatable.Rows.Add(datarow);
                }
            }

            return datatable;
        }


        [WebMethod(EnableSession = true)]
        public WebServiceResult GetList(string sourcetable, string pov, string listcolumns)
        {
            Logging.WriteLog(LoggingLevel.DEBUG, "GetList (sourcetable='{0}', pov='{1}', listcolumns='{2}'",
                                                                            sourcetable, pov, listcolumns);

            DBAgent agent = new DBAgent();

            try
            {
                POVManager povManager = new POVManager(sourcetable, "", pov, "", "", listcolumns, "", "");

                agent.Connect();

                string result = agent.GetListResult(povManager.SourceTable.First().Value.Name, String.Join(" ", povManager.GetPOVDefine()), povManager.GetListDefine(),
                    povManager.GetListColumnsIndex(), povManager.GetListRowsIndex());

                return new WebServiceResult(WebServiceModuleCode.ODS, WebServiceErrorCode.Success, result);
            }
            catch (WebServiceException ex)
            {
                return ex.Result;
            }
            catch (Exception ex)
            {
                Logging.WriteLog(LoggingLevel.ERROR, "{0}", ex.ToString());
                return new WebServiceResult(WebServiceModuleCode.ODS, WebServiceErrorCode.Error_Retrieve_Failture, ex.ToString());
            }
            finally
            {
                agent.Disconnct();
            }
        }


        [WebMethod(EnableSession = true)]
        public WebServiceResult ClearData(String system, string entity, string scenario, string year, string period)
        {
            Logging.WriteLog(LoggingLevel.DEBUG, "LoadData(System={0}, Entity={1}, Scenario={2}, Year={3}, Period={4})",
                                                                         system, entity, scenario, year, period);

            DBAgent agent = new DBAgent();

            try
            {
                agent.Connect();
                string[] allSqls = ConfigurationManager.AppSettings["ODS:ClearData:SQL"].Split(';');

                foreach (string sql in allSqls)
                {
                    agent.ExecCommand(sql, scenario, entity, year, period);
                }

                return new WebServiceResult(WebServiceModuleCode.ODS, WebServiceErrorCode.Success, "");
            }
            catch (WebServiceException ex)
            {
                return ex.Result;
            }
            catch (Exception ex)
            {
                Logging.WriteLog(LoggingLevel.ERROR, "{0}", ex.ToString());
                return new WebServiceResult(WebServiceModuleCode.ODS, WebServiceErrorCode.Error_Retrieve_Failture, ex.ToString());
            }
            finally
            {
                agent.Disconnct();
            }
        }



        [WebMethod(EnableSession = true)]
        public WebServiceResult LoadData(String system, string entity, string scenario, string year, string period)
        {
            Logging.WriteLog(LoggingLevel.DEBUG, "LoadData(System={0}, Entity={1}, Scenario={2}, Year={3}, Period={4})",
                system, entity, scenario, year, period);

            try
            {
                switch (ConfigurationManager.AppSettings["ODS:LoadData"].ToString())
                {
                    case "FDM":
                        return LoadDataViaFDM(system, entity, scenario, year, period);

                    default:
                        return LoadDataViaText(system, entity, scenario, year, period);
                }
            }
            catch (WebServiceException ex)
            {
                return ex.Result;
            }
            catch (Exception ex)
            {
                Logging.WriteLog(LoggingLevel.ERROR, "{0}", ex.ToString());
                return new WebServiceResult(WebServiceModuleCode.ODS, WebServiceErrorCode.Error_Retrieve_Failture, ex.ToString());
            }
            finally
            {
            }
        }

        /// <summary>
        /// 加载数据到目标系统
        /// </summary>
        /// <param name="system">目标系统，如"HFM"</param>
        /// <param name="entity">实体</param>
        /// <param name="scenario">场景</param>
        /// <param name="year">年</param>
        /// <param name="period">月份</param>
        /// <returns></returns>
        public WebServiceResult LoadDataViaText(String system, string entity, string scenario, string year, string period)
        {
            Logging.WriteLog(LoggingLevel.DEBUG, "LoadData(System={0}, Entity={1}, Scenario={2}, Year={3}, Period={4})", 
                system, entity, scenario, year, period);

            DBAgent agent = new DBAgent();
            HFMProxy HFMProxy = InitAgent();

            try
            {
                agent.Connect();

                agent.LoadData(system, entity, scenario, year, period);
                StringBuilder text = agent.LoadSQL(ConfigurationManager.AppSettings["ODS:LoadData:SQL"],
                                                    ConfigurationManager.AppSettings["ODS:LoadData:Spliter"], 
                                                    scenario, entity, year, period);

                HFMLogin(HFMProxy);

                String sDataFile = Server.MapPath(String.Format("Data\\{0}_{1}_{2}_{3}.txt", scenario, entity, year, period));
                String sLogFile = Server.MapPath(String.Format("Data\\{0}_{1}_{2}_{3}.log", scenario, entity, year, period));
                HFMProxy.SetFileContent(sDataFile, String.Format("{0}\r\n{1}", ConfigurationManager.AppSettings["ODS:LoadData:Header"], text.ToString()));

                Logging.WriteLog(LoggingLevel.DEBUG, "LoadData(DataFile={0}, LogFile={1}", sDataFile, sLogFile);
                HFMProxy.LoadData(sDataFile, sLogFile);
                string sMessage = HFMProxy.GetFileContent(sLogFile).ToLower();

                foreach (string sErrorFlag in ConfigurationManager.AppSettings["ODS:LoadData:ErrorFlag"].ToLower().Split(';'))
                {
                    if (sMessage.IndexOf(sErrorFlag) > 0)
                    {
                        return new WebServiceResult(WebServiceModuleCode.ODS, WebServiceErrorCode.Error_Retrieve_Failture, sMessage);
                    }
                }

                return new WebServiceResult(WebServiceModuleCode.ODS, WebServiceErrorCode.Success, "");
            }
            catch (WebServiceException ex)
            {
                return ex.Result;
            }
            catch (Exception ex)
            {
                Logging.WriteLog(LoggingLevel.ERROR, "{0}", ex.ToString());
                return new WebServiceResult(WebServiceModuleCode.ODS, WebServiceErrorCode.Error_Retrieve_Failture, ex.ToString());
            }
            finally
            {
                agent.Disconnct();
            }
        }



        /// <summary>
        /// LoadDataViaFDM
        /// </summary>
        /// <param name="system">目标系统，如"HFM"</param>
        /// <param name="entity">实体</param>
        /// <param name="scenario">场景</param>
        /// <param name="year">年</param>
        /// <param name="period">月份</param>
        /// <returns></returns>
        public WebServiceResult LoadDataViaFDM (String system, string entity, string scenario, string year, string period)
        {
            Logging.WriteLog(LoggingLevel.DEBUG, "LoadDataViaFDM(System={0}, Entity={1}, Scenario={2}, Year={3}, Period={4})",
                system, entity, scenario, year, period);

            try
            {
                String application, username, password, fdmlocation, fdmcategory, fdmperiod;
                MideaFDMService.ReturnResult result = new MideaFDMService.ReturnResult();

                application = ConfigurationManager.AppSettings["ODS:FDM:Application"];
                username = ConfigurationManager.AppSettings["ODS:FDM:Username"] == "" ? this.Session["Username"].ToString() : ConfigurationManager.AppSettings["ODS:FDM:Username"];
                password = ConfigurationManager.AppSettings["ODS:FDM:Password"] == "" ? this.Session["Password"].ToString() : ConfigurationManager.AppSettings["ODS:FDM:Password"];
                fdmlocation = ConfigurationManager.AppSettings["ODS:FDM:Location"] == "" ? entity : ConfigurationManager.AppSettings["ODS:FDM:Location"];
                fdmcategory = ConfigurationManager.AppSettings["ODS:FDM:Category"] == "" ? scenario : ConfigurationManager.AppSettings["ODS:FDM:Category"];
                fdmperiod = ConfigurationManager.AppSettings["ODS:FDM:Period"] == "" ? string.Format("{1} - {0}", year, period.Substring(0, 3)) : ConfigurationManager.AppSettings["ODS:FDM:Period"];

                Logging.WriteLog(LoggingLevel.DEBUG, "LoadDataViaFDM({0}, {1}, {2}, {3}, {4}, {5})", application, username, password, fdmlocation, fdmcategory, fdmperiod);

                MideaFDMService.MDFDMServiceSoapClient client = new MideaFDMService.MDFDMServiceSoapClient();
                client.Endpoint.Address = new System.ServiceModel.EndpointAddress(ConfigurationManager.AppSettings["ODS:FDM:Service"]);
                Logging.WriteLog(LoggingLevel.DEBUG, "Connect to FDM service: {0}", client.Endpoint.Address);

                do
                {
                    result = client.ImportByGobjAS("", application, username, password, fdmlocation, fdmcategory, fdmperiod);
                    Logging.WriteLog(LoggingLevel.DEBUG, "ImportByGobjAS({0}, {1}, {2}, {3}, {4}, {5}) = ({6}, {7})", application, username, password, fdmlocation, fdmcategory, fdmperiod, result.result, result.resultMsg);
                    if (result.result == false) break;

                    result = client.ValidateByGobjAS(application, username, password, fdmlocation, fdmcategory, fdmperiod);
                    Logging.WriteLog(LoggingLevel.DEBUG, "ValidateByGobjAS({0}, {1}, {2}, {3}, {4}, {5}) = ({6}, {7})", application, username, password, fdmlocation, fdmcategory, fdmperiod, result.result, result.resultMsg);
                    if (result.result == false) break;

                    result = client.ExportByGobjAS(application, username, password, fdmlocation, fdmcategory, fdmperiod);
                    Logging.WriteLog(LoggingLevel.DEBUG, "ExportByGobjAS({0}, {1}, {2}, {3}, {4}, {5}) = ({6}, {7})", application, username, password, fdmlocation, fdmcategory, fdmperiod, result.result, result.resultMsg);
                    if (result.result == false) break;

                } while (false);

                if (result.result == true)
                { 
                    return new WebServiceResult(WebServiceModuleCode.ODS, WebServiceErrorCode.Success, "");
                }
                else
                {
                    return new WebServiceResult(WebServiceModuleCode.ODS, WebServiceErrorCode.Error_Load_Journals_Failtrue, result.resultMsg);
                }
                
            }
            catch (WebServiceException ex)
            {
                return ex.Result;
            }
            catch (Exception ex)
            {
                Logging.WriteLog(LoggingLevel.ERROR, "{0}", ex.ToString());
                return new WebServiceResult(WebServiceModuleCode.ODS, WebServiceErrorCode.Error_Retrieve_Failture, ex.ToString());
            }
            finally
            {
            }
        }

        /// <summary>
        /// Description:  
        /// Calls:            
        /// Called By:     
        /// Parameter:   
        /// Others:         
        /// </summary>
        /// <returns></returns>
        [WebMethod(EnableSession = true)]
        public WebServiceResult Retrieve(string sourcetable, string targetable, string pov, string column, string row, string datafiled)
        {
            Logging.WriteLog(LoggingLevel.DEBUG, "Retrieve (sourcetable='{0}', targettable='{1}', pov='{2}', column='{3}', row='{4}', datafield='{5}'", 
                                                                            sourcetable, targetable, pov, column, row, datafiled);

            DBAgent agent = new DBAgent();
            POVManager povManager = null;
            string results = "";

            try
            {
                povManager = new POVManager(sourcetable, targetable, pov, column, row, "", datafiled, "");

                agent.CheckDimension(Server.MapPath(ConfigurationManager.AppSettings["ODS:MetaTableFile"]), povManager.SourceTable.First().Value.Name, povManager.GetKeyColumns(), povManager.GetDataColumns(), "GET");

                DataTable datatable = GetDataTable(povManager);

                if (datatable.Rows.Count > 0)
                {
                    agent.Connect();

                    agent.BeforeQuery(povManager.SourceTable.First().Value.Name);

                    agent.BatchQuery(povManager.SourceTable.First().Value.Name, povManager.GetKeyColumns(), povManager.GetDataColumns(), new string[] { "ColumnIDX", "RowIDX", "FieldName", "FieldValue", "Username" }, datatable, 200);

                    agent.CheckMetaData(Server.MapPath(ConfigurationManager.AppSettings["ODS:MetaTableFile"]), povManager.SourceTable.First().Value.Name, povManager.SourceTable.First().Value.Name, agent.TablePrefix, "GET");

                    foreach (string columnname in povManager.GetDataColumns())
                    {
                        agent.QueryData(povManager.SourceTable.First().Value.Name, String.Join("and", povManager.GetComparsion()), columnname, string.Join("", povManager.GetPOVDefine()));
                    }

                    agent.MoveHistory(povManager.SourceTable.First().Value.Name);

                    results = agent.GetQueryResult(povManager.SourceTable.First().Value.Name);
                }

                return new WebServiceResult(WebServiceModuleCode.ODS, WebServiceErrorCode.Success, results);

            }
            catch (WebServiceException ex)
            {
                return ex.Result;
            }
            catch (Exception ex)
            {
                Logging.WriteLog(LoggingLevel.ERROR, "{0}", ex.ToString());
                return new WebServiceResult(WebServiceModuleCode.ODS, WebServiceErrorCode.Error_Retrieve_Failture, ex.ToString());
            }
            finally
            {
                try
                {
                    agent.AfterQuery(povManager.SourceTable.First().Value.Name);
                }
                catch (Exception ex)
                {
                    Logging.WriteLog(LoggingLevel.WARNING, "{0}", ex.ToString());
                }

                agent.Disconnct();
            }
        }

        public DataTable GetDataTable(POVManager povManager)
        {
            DataTable datatable = new DataTable();

            datatable.TableName = povManager.SourceTable.First().Value.Name;

            datatable.Columns.Add(new DataColumn("FieldName", Type.GetType("System.String")));
            datatable.Columns.Add(new DataColumn("FieldValue", Type.GetType("System.String")));

            foreach (string columnname in povManager.GetKeyColumns())
            {
                DataColumn columnitem = new DataColumn(columnname, Type.GetType("System.String"));
                datatable.Columns.Add(columnitem);
            }
            foreach (string columnname in povManager.GetExtraColumns())
            {
                DataColumn columnitem = new DataColumn(columnname, Type.GetType("System.String"));
                datatable.Columns.Add(columnitem);
            }

            if (povManager.Columns.Count > 0)
            {
                Logging.WriteLog(LoggingLevel.DEBUG, "Single Datafield Mode");

                foreach (RangeDefine each_col in povManager.Columns.Values)
                {
                    foreach (RangeDefine each_row in povManager.Rows.Values)
                    {
                        RangeDefine each_key = povManager.DataFields.Values.First();
                        DataRow datarow = datatable.NewRow();

                        datarow["ColumnIDX"] = each_col.Column;
                        datarow["RowIDX"] = each_row.Row;
                        datarow["FieldName"] = each_key.Name;
                        datarow["FieldValue"] = ConfigurationManager.AppSettings["ODS:MISSINGFLAG"];
                        datarow["Username"] = Session["Username"];

                        foreach (RangeDefine each_pov in povManager.POVs.Values)
                        {
                            datarow[each_pov.Name] = each_pov.Value;
                        }

                        string[] names = (each_col.Name + "$" + each_row.Name).Trim('$').Split('$');
                        string[] values = (each_col.Value + "$" + each_row.Value).Trim('$').Split('$');

                        if (names.Length != values.Length)
                        {
                            throw new WebServiceException(WebServiceErrorCode.Error_Retrieve_Failture, String.Format("({0}, {1}) col.name={2}, col.value={3}, row.name={4}, row.value={5}", each_col.Column, each_col.Row, each_col.Name, each_col.Value, each_row.Name, each_row.Value));
                        }

                        for (int index = 0; index < names.Length; ++index)
                        {
                            datarow[names[index]] = values[index];
                        }

                        datatable.Rows.Add(datarow);
                    }
                }
            }
            else
            {
                Logging.WriteLog(LoggingLevel.DEBUG, "Multiple Datafield Mode");

                foreach (RangeDefine each_row in povManager.Rows.Values)
                {
                    foreach (RangeDefine each_key in povManager.DataFields.Values)
                    {
                        DataRow datarow = datatable.NewRow();
                        
                        datarow["ColumnIDX"] = each_key.Column;
                   
                        datarow["RowIDX"] = each_row.Row;
                        datarow["FieldName"] = each_key.Name;
                        datarow["FieldValue"] = ConfigurationManager.AppSettings["ODS:MissingFlag"];
                        datarow["Username"] = Session["Username"];

                        foreach (RangeDefine each_pov in povManager.POVs.Values)
                        {
                            datarow[each_pov.Name] = each_pov.Value;
                        }

                        string[] names = each_row.Name.Trim('$').Split('$');
                        string[] values = each_row.Value.Trim('$').Split('$');

                        if (names.Length != values.Length)
                        {
                            throw new WebServiceException(WebServiceErrorCode.Error_Retrieve_Failture, String.Format("({0}, {1}) field.name={2}, field.value={3}, row.name={4}, row.value={5}", each_key.Column, each_row.Row, each_key.Name, each_key.Value, each_row.Name, each_row.Value));
                        }

                        for (int index = 0; index < names.Length; ++index)
                        {
                            datarow[names[index]] = values[index];
                        }

                        datatable.Rows.Add(datarow);
                    }
                }
            }


            return datatable;
 
        }
        
    }

    public class RangeDefine
    {
        public string Column;
        public string Row;
        public string Name;
        public string Value;
        public string Property;
    }

    public class POVManager
    {
        public Dictionary<string, RangeDefine> SourceTable = new Dictionary<string, RangeDefine>();
        public Dictionary<string, RangeDefine> TargetTable = new Dictionary<string, RangeDefine>();
        public Dictionary<string, RangeDefine> POVs = new Dictionary<string, RangeDefine>();
        public Dictionary<string, RangeDefine> Columns = new Dictionary<string, RangeDefine>();
        public Dictionary<string, RangeDefine> Rows = new Dictionary<string, RangeDefine>();
        public Dictionary<string, RangeDefine> ListFields = new Dictionary<string, RangeDefine>();
        public Dictionary<string, RangeDefine> DataFields = new Dictionary<string, RangeDefine>();
        public Dictionary<string, RangeDefine> DataValues = new Dictionary<string, RangeDefine>();

        public POVManager(string sourcetable, string targettable, string pov, string column, string row, string listfields, string datafields, string datavalues)
        {
            InitRangeList(SourceTable, sourcetable);
            InitRangeList(TargetTable, targettable);
            InitRangeList(POVs, pov);
            InitRangeList(Columns, column);
            InitRangeList(Rows, row);
            InitRangeList(ListFields, listfields);
            InitRangeList(DataFields, datafields);
            InitRangeList(DataValues, datavalues);
        }

        public static void InitRangeList(Dictionary<string, RangeDefine> target, string value)
        {
            String[] result = value.Split(';');
            if (result.Length <= 1)
            {
                return;
            }
            else if (result.Length != 3)
            {
                throw new WebServiceException(WebServiceErrorCode.Error_Retrieve_Failture, "Value Data != 3, \n" + value);
            }
            else
            {
            }

            String[] Indexs = result[0].Split(',');
            String[] Keys = result[1].Split(',');

            //临时版本----------- 屏蔽CO., LTD的问题
            string[] strReplacePatterns = ConfigurationManager.AppSettings["ODS:Formula:Replace"].Split(new string[] {"!!!"}, StringSplitOptions.RemoveEmptyEntries);
            foreach (string strRel in strReplacePatterns)
            {
                string[] strReplacePatternItem = strRel.Split(new string[]{"???"}, StringSplitOptions.RemoveEmptyEntries);
                result[2] = result[2].Replace(strReplacePatternItem[0], strReplacePatternItem[1]);
            }
            
            String[] Values = result[2].Split(',');

            if (Indexs.Length != Keys.Length || Indexs.Length != Values.Length)
            {
                throw new WebServiceException(WebServiceErrorCode.Error_Retrieve_Failture, String.Format ("Length not same. {0}, {1}, {2}", Indexs.Length, Keys.Length, Values.Length));
            }

            target.Clear();
            string[] temp;
            for (int index = 0; index < Indexs.Length; ++index)
            {
                //临时版本----------- 屏蔽CO., LTD的问题
                foreach (string strRel in strReplacePatterns)
                {
                    string[] strReplacePatternItem = strRel.Split(new string[] { "???" }, StringSplitOptions.RemoveEmptyEntries);
                    Values[index] = Values[index].Replace(strReplacePatternItem[1], strReplacePatternItem[0]);
                }
                if (Indexs[index].Trim() == "")
                {
                }
                else if (Indexs[index].Contains("%"))
                {
                    temp = Indexs[index].Split('%');
                    target[Indexs[index]] = new RangeDefine { Column = temp[0] , Row = temp[1], Name = Keys[index], Property = "", Value = Values[index] };
                }
                else
                {
                    target[Indexs[index]] = new RangeDefine { Column = Indexs[index], Row = Indexs[index], Name = Keys[index], Property = "", Value = Values[index] };
                }
            }
        }
    
    
        public string[] GetKeyColumns ()
        {
            String keycolumns = "";
            if (Columns.Count > 0)
            {
                keycolumns = Columns.First().Value.Name + "$" + Rows.First().Value.Name;
            }
            else
            {
                keycolumns = Rows.First().Value.Name;
            }

            foreach (RangeDefine range in POVs.Values)
            {
                keycolumns = keycolumns + "$" + range.Name;
            }

            return keycolumns.Replace("$$", "$").Trim('$').Split('$');
        }

        public string[] GetDataColumns()
        {
            string datacolumns = "";
            foreach (RangeDefine range in DataFields.Values)
            {
                datacolumns = datacolumns + "$" + range.Name;
            }
            return datacolumns.Replace("$$", "$").Trim('$').Split('$');
        }

        public List<String> GetComparsion()
        {
            List<String> comparsion = new List<string>();
            foreach (string columnname in GetKeyColumns())
            {
                comparsion.Add(string.Format(" A.{0}=B.{0} ", columnname));
            }
            return comparsion;
        }

        public List<String> GetPOVDefine()
        {
            List<string> povdefine = new List<string>();
            foreach (RangeDefine each_pov in POVs.Values)
            {
                povdefine.Add(String.Format(" and B.{0}='{1}' ", each_pov.Name, each_pov.Value));
            }
            return povdefine;
        }

        public List<String> GetKeySet()
        {
            List<String> keyset = new List<string>();
            foreach (string columnname in GetDataColumns())
            {
                keyset.Add(string.Format(" A.{0}=B.{0} ", columnname, columnname));
            }
            return keyset;
        }

        public List<String> GetColumns()
        {
            List<string> columns = new List<string>();

            foreach (string columnname in GetKeyColumns())
            {
                columns.Add(string.Format(" {0} ", columnname));
            }
            foreach (string columnname in GetDataColumns())
            {
                columns.Add(string.Format("{0}", columnname));
            }
            return columns;
        }

        public List<string> GetExtraColumns()
        {
            return new String[] { "ColumnIDX", "RowIDX", "Username" }.ToList();
        }

        public string GetListDefine()
        {
            List<string> listfields = new List<string>();
            foreach (RangeDefine field in ListFields.Values)
            {
                listfields.Add(field.Name);
            }
            return String.Join(",", listfields) ;
        }

        public List<string> GetListColumnsIndex()
        {
            List<string> columnindexs = new List<string>();
            foreach (RangeDefine field in ListFields.Values)
            {
                columnindexs.Add(field.Column);
            }
            return columnindexs;
        }

        public List<string> GetListRowsIndex()
        {
            List<string> rowindexs = new List<string>();
            string rowtext = "";
            foreach (RangeDefine field in ListFields.Values)
            {
                rowtext = field.Value;
                if (rowtext.Trim().Length > 0)
                {
                    break;
                }
            }

            String[] range = rowtext.Split('-');
            if (range.Length == 2)
            {
                for (int rowindex = int.Parse(range[0]); rowindex < int.Parse(range[1]); ++rowindex)
                {
                    rowindexs.Add(rowindex.ToString());
                }
            }

            return rowindexs;
        }



    }


}
